package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Page;
import com.xxxy.jsq.model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao implements BaseDao<User> {
    private C3P0Utils db = null;

    public UserDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(User model) {
        Connection conn = null; // 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append("insert into pa_user(user_name, user_pwd, teacher_id, role_id, user_flag) ");
        sqlStr.append("values(?,?,?,?,?) ");
        conn = db.getConnection();
        // 调用同名检测
        boolean flag = isSame(model, true);
        if (flag == false) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getUserName());
                pstm.setString(2, model.getUserPwd());
                pstm.setLong(3, model.getTeacherId());
                pstm.setLong(4, model.getRoleId());
                pstm.setString(5, model.getUserFlag());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                return "数据库操作异常";  // 数据库操作异常
            }
            if (num > 0) {
                return "Ok";
            } else {
                return "插入失败";  // 插入失败
            }
        } else {
            return "Same";  // 用户名已存在
        }
    }


    @Override
    public String update(User model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        boolean flag = isSame(model, false);
        if (flag == true) {
            return "Same";
        }
        sqlStr.append("update pa_user set ");
        if (model.getUserName() != null && !model.getUserName().equals("")) {
            sqlStr.append(" user_name='" + model.getUserName() + "'");
        }
        if (model.getUserPwd() != null && !model.getUserPwd().equals("")) {
            sqlStr.append(" ,user_pwd='" + model.getUserPwd() + "'");
        }
        if (model.getTeacherId() != null) {
            sqlStr.append(" ,teacher_id='" + model.getTeacherId() + "'");
        }
        if (model.getRoleId() != null) {
            sqlStr.append(" ,role_id='" + model.getRoleId() + "'");
        }
        if (model.getUserFlag() != null && !model.getUserFlag().equals("")) {
            sqlStr.append(" ,user_flag='" + model.getUserFlag() + "'");
        }
        sqlStr.append(" where user_id=? ");
        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getUserId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public String delete(User model) {
        return null;
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append("delete from pa_user where user_id=? ");
        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public List<User> getAllData(User model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        List<User> list = new ArrayList<User>();
        sqlStr.append(" SELECT * from pa_user ");
        sqlStr.append("where 1=1 ");
        if (model.getUserName() != null && !model.getUserName().equals("")) {
            sqlStr.append("and user_name='" + model.getUserName() + "' ");
        }
        if (model.getUserFlag() != null && !model.getUserFlag().equals("")) {
            sqlStr.append(" and user_flag='" + model.getUserFlag() + "' ");
        }
        if (model.getTeacherId() != null) {
            sqlStr.append(" and teacher_id='" + model.getTeacherId() + "' ");
        }
        if (model.getRoleId() != null) {
            sqlStr.append(" and role_id='" + model.getRoleId() + "' ");
        }
        sqlStr.append(" ORDER BY user_id asc, user_name asc ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                User md = new User();
                md.setUserId(rs.getLong("user_id"));
                md.setUserName(rs.getString("user_name"));
                md.setUserPwd(rs.getString("user_pwd"));
                md.setTeacherId(rs.getLong("teacher_id"));
                md.setRoleId(rs.getLong("role_id"));
                md.setUserFlag(rs.getString("user_flag"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

//



    @Override
    public User findById(Long cid) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append(" SELECT * from pa_user ");
        sqlStr.append("where 1=1 and user_id=? ");
        conn = db.getConnection();
        User md = new User();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                md.setUserId(rs.getLong("user_id"));
                md.setUserName(rs.getString("user_name"));
                md.setUserPwd(rs.getString("user_pwd"));
                md.setTeacherId(rs.getLong("teacher_id"));
                md.setRoleId(rs.getLong("role_id"));
                md.setUserFlag(rs.getString("user_flag"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public User find(User model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append(" SELECT * from pa_user ");
        sqlStr.append("where 1=1 ");
        if (model.getUserName() != null && !model.getUserName().equals("")) {
            sqlStr.append("and user_name='" + model.getUserName() + "' ");
        }
        if (model.getUserFlag() != null && !model.getUserFlag().equals("")) {
            sqlStr.append(" and user_flag='" + model.getUserFlag() + "' ");
        }
        if (model.getTeacherId() != null) {
            sqlStr.append(" and teacher_id='" + model.getTeacherId() + "' ");
        }
        if (model.getRoleId() != null) {
            sqlStr.append(" and role_id='" + model.getRoleId() + "' ");
        }
        conn = db.getConnection();
        User md = new User();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();// 执行查询操作select
            if (rs.next()) {
                md.setUserId(rs.getLong("user_id"));
                md.setUserName(rs.getString("user_name"));
                md.setUserPwd(rs.getString("user_pwd"));
                md.setTeacherId(rs.getLong("teacher_id"));
                md.setRoleId(rs.getLong("role_id"));
                md.setUserFlag(rs.getString("user_flag"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return md;
    }

    @Override
    public boolean isSame(User model, boolean sflag) {
        Connection conn = null; // 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append(" select * from pa_user where user_name=? ");
        if (sflag == false) {
            sqlStr.append("and user_id!='" + model.getUserId() + "' ");
        }
        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getUserName());
            rs = pstm.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<User> getAllDataByPage(User model, Page page) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        List<User> list = new ArrayList<User>();
        sqlStr.append("SELECT * from pa_user ");
        sqlStr.append("where 1=1 ");
        if (model.getUserName() != null && !model.getUserName().equals("")) {
            sqlStr.append("and user_name like '%" + model.getUserName() + "%' ");
        }
        if (model.getUserFlag() != null && !model.getUserFlag().equals("")) {
            sqlStr.append(" and user_flag='" + model.getUserFlag() + "' ");
        }
        if (model.getTeacherId() != null) {
            sqlStr.append(" and teacher_id='" + model.getTeacherId() + "' ");
        }
        if (model.getRoleId() != null) {
            sqlStr.append(" and role_id='" + model.getRoleId() + "' ");
        }
        sqlStr.append(" ORDER BY user_id asc, user_name asc ");
        sqlStr.append(" limit ?,? ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());// 起始位置
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                User md = new User();
                md.setUserId(rs.getLong("user_id"));
                md.setUserName(rs.getString("user_name"));
                md.setUserPwd(rs.getString("user_pwd"));
                md.setTeacherId(rs.getLong("teacher_id"));
                md.setRoleId(rs.getLong("role_id"));
                md.setUserFlag(rs.getString("user_flag"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(User model) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        sqlStr.append("select count(*) from pa_user ");
        sqlStr.append("where 1=1 ");
        if (model.getUserName() != null && !model.getUserName().equals("")) {
            sqlStr.append("and user_name='" + model.getUserName() + "' ");
        }
        if (model.getUserFlag() != null && !model.getUserFlag().equals("")) {
            sqlStr.append(" and user_flag='" + model.getUserFlag() + "' ");
        }
        if (model.getTeacherId() != null) {
            sqlStr.append(" and teacher_id='" + model.getTeacherId() + "' ");
        }
        if (model.getRoleId() != null) {
            sqlStr.append(" and role_id='" + model.getRoleId() + "' ");
        }
        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<User> getAllky() {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        List<User> list = new ArrayList<User>();
        sqlStr.append("select * from pa_user where user_flag='0' ");//0启用，1禁用
        sqlStr.append("order by user_name asc ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                User md = new User();
                md.setUserId(rs.getLong("user_id"));
                md.setUserName(rs.getString("user_name"));
                md.setUserPwd(rs.getString("user_pwd"));
                md.setTeacherId(rs.getLong("teacher_id"));
                md.setRoleId(rs.getLong("role_id"));
                md.setUserFlag(rs.getString("user_flag"));
                list.add(md);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
